package org.acooke.uykfd.db

import com.mysql.jdbc.{Driver => MySqlDriver}

import java.io.File
import java.sql.Connection
import java.sql.DriverManager

import org.apache.empire.commons.ErrorObject
import org.apache.empire.db.DBSQLScript
import org.apache.empire.db.hsql.DBDatabaseDriverHSql
import org.apache.empire.db.mysql.DBDatabaseDriverMySQL

import org.hsqldb.{jdbcDriver => HsqlDriver}

/**
 * This should be created once.  It instantiates the database and provides
 * a connection.
 *
 * This is a bit ugly.  Part of the problem is that I cannot see how to
 * delete HSQL databases, so it's difficult to provide object-like
 * semantics.
 *
 * The close method must be called at the end of the program to make sure
 * that the data is persisted (HSQL is in-memory while being used).
 */
class Database {

  val uri = Schema.PROPERTIES.orm.DB_URL(null).getString
  val user = Schema.PROPERTIES.orm.DB_USER(null).getString
  val pwd = Schema.PROPERTIES.orm.DB_PASSWORD(null).getString

  ErrorObject.setExceptionsEnabled(true)

  val dbType = uri.split(":")(1)

  val driver = dbType match {
    case "hsqldb" => {
      new HsqlDriver; 
      new DBDatabaseDriverHSql
    }
    case "mysql" => {
      new MySqlDriver;
      val mySqlDriver = new DBDatabaseDriverMySQL
//      mySqlDriver.setDatabaseName(uri.substring(uri.lastIndexOf("/")+1))
      mySqlDriver.setDatabaseName("uykfd")
      mySqlDriver
    }
    case _ => throw new RuntimeException("Database not supported")
  }

  val cnxn = DriverManager.getConnection(uri, user, pwd)
  try {
    if (! Schema.isOpen) Schema.open(driver, cnxn)
    val cmd = Schema.createCommand
    cmd.select(Schema.TRACKS.count)
    Schema.querySingleInt(cmd.getSelect, -1, cnxn)
  } catch {
    case e: Exception => {
      val script = new DBSQLScript()
      Schema.getCreateDDLScript(driver, script)
      script.run(driver, cnxn, false)
      if (dbType == "hsqldb") {
        Schema.executeSQL("SET PROPERTY \"sql.enforce_strict_size\" true", cnxn)
        Schema.commit(cnxn)
      }
    }
  }
  if (! Schema.isOpen) Schema.open(driver, cnxn)
  // update these so that properties gives the correct data to the user
  Schema.PROPERTIES.orm.DB_URL(cnxn).pushValue(uri)
  Schema.PROPERTIES.orm.DB_USER(cnxn).pushValue(user)
  Schema.PROPERTIES.orm.DB_PASSWORD(cnxn).pushValue(pwd)

  def close {
    cnxn.close
  }

}

